   //
//  BIDSavedInTheDataBase.m
//  随手记01
//
//  Created by 邵瑞 on 13-5-1.
//  Copyright (c) 2013年 邵瑞. All rights reserved.
//

#import "BIDDatabaseManager.h"
#import "FMDatabase.h"
#import "FMDatabaseAdditions.h"
#define kClockTable @"clockTable"
#define kIsReadDatabase @"isReadDatabase8"
#define kTableName @"MessageInfoTable"//保存用户输入数据的表
#define kTypeTable @"typeTableInfo"//主表
#define kSubTypeTable @"subTypeInfo"//子表
//#define kTypeNameTable @"typeTableInfo"
#define kFileNameKeys @"KPTestFile1.text"//数据库名
//acount time type subType payoutId(int) other month(int)   
@implementation BIDDatabaseManager
@synthesize fmdbDataBase = _fmdbDataBase;
+(BIDDatabaseManager *)SharedOperator//BIDOffLineModule是返回的一个对象
{
    static BIDDatabaseManager *aDatabase=nil;
    static dispatch_once_t predicate;//一个命令
    dispatch_once(&predicate, ^
    {//整个生命期子被调用一次
    
        aDatabase = [[self alloc] init];//
    });
    return aDatabase;
}
-(id) init
{
    isReadDatabase=NO;
    self = [super init];
    if (self)
    {
//        self.fmdbDataBase = [[FMDatabase alloc] initWithPath: [self ReadSqlFileName:kFileNameKeys]];
 FMDatabase *database = [[FMDatabase alloc] initWithPath: [self readDb:@"KPTestFile1.text"]];
        self.fmdbDataBase =database;


        if(![self.fmdbDataBase open])
        {
            NSLog(@"数据库打开失败");
        }
        NSString * CreateTypeTable = [NSString stringWithFormat:@"create table if not exists %@(typeInfo_ID integer primary key autoincrement,typename text)",kTypeTable];
        NSString *CreateSubTypeTable = [NSString stringWithFormat:@"create table if not exists %@(subclassInfo_ID integer primary key autoincrement,refer_ID integer,subTypename text,foreign key(refer_ID) references %@(typeInfo_ID))",kSubTypeTable,kTypeTable];
//        NSString *CreateTableSaveMessage = [NSString stringWithFormat:@"create table if not exists %@ (messageInfo_ID integer primary key autoincrement,amount integer,type text,subType text,time text,other text,month integer,foreign key (type) references %@(typename) ,foreign key(subType) references %@(subTypename))",kTableName,kTypeTable,kSubTypeTable];
        NSString *userInfo=[NSString stringWithFormat:@"create table if not exists %@ (messageInfo_ID integer primary key autoincrement,amount double,type_ID integer,subType_ID integer,time text,other text,whichWay integer,foreign key (type_ID) references %@(typeInfo_ID) ,foreign key(subType_ID) references %@(subclassInfo_ID))",kTableName,kTypeTable,kSubTypeTable];
        NSString *clockInfo=[NSString stringWithFormat:@"create table if not exists %@(clock_ID integer primary key autoincrement,time text,type_id integer ,subType_id integer,amount double ,other text,foreign key (type_id) references %@(typeInfo_ID) ,foreign key(subType_id) references %@(subclassInfo_ID))",kClockTable,kTypeTable,kSubTypeTable];
        [self.fmdbDataBase executeUpdate:clockInfo];
       [self.fmdbDataBase executeUpdate:CreateTypeTable];
        [self.fmdbDataBase executeUpdate:CreateSubTypeTable];
      BOOL execute=  [self.fmdbDataBase executeUpdate:userInfo];
        
        if  (execute)
            //execute执行
        {
            
                        NSLog(@"间表成功");
        }
        
           
    }
    
    return self;
}

-(NSString *)readDb:(NSString *)name
{
    BOOL success;
    NSFileManager *manager=[NSFileManager defaultManager];
    NSError *error;
    NSArray *paths=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *Document=[paths objectAtIndex:0];
    NSString *writeDBPath=[Document stringByAppendingPathComponent:name];
    success = [manager fileExistsAtPath:writeDBPath];
    if(!success)
    {
        NSString *defaultDBPath=[[[NSBundle mainBundle]resourcePath]stringByAppendingPathComponent:name];
        success = [manager copyItemAtPath:defaultDBPath toPath:writeDBPath error:&error];
//        success =[manager copyItemAtPath:defaultDBPath toPath:writeDBPath error:&error];
        if(!success)
        {
            NSLog(@"%@",[error localizedDescription]);
        }
    }
    return writeDBPath;
}

//-(NSString *)ReadSqlFileName :(NSString *)dbPathFile//dbPathFile参数接受的是#define kFileNameKey @"FMDBBaseA1.db"//的数据库名
//{
//    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);//找到文件的路径所在地
//    NSString *documentDirectory = [paths objectAtIndex:0];
//    NSString * dbPath = [documentDirectory stringByAppendingPathComponent:dbPathFile];
//    return  dbPath;
//}
/*
//插入数据
-(BOOL)insertAPayoutOrTypeOrSubtype:(BIDABill *)aPayout OrType:(NSString *)type orSubtype:(NSString *)subtype
{  
    BOOL judge;
    if (aPayout) {
        
    
    NSString *inset=[NSString stringWithFormat:@"insert into %@ (amount,type,subType,time,other,month) values ('%@','%@','%@','%@','%@','%d')",kTableName,aPayout.Camount,aPayout.type,aPayout.subType,aPayout.Ctime,aPayout.Cdescription,aPayout.Cmonth];
     judge = [self.fmdbDataBase executeUpdate:inset];
    }
    else if(type&&!subtype){
        NSString *typeInsert=[NSString stringWithFormat:@"insert into %@(typename) values('%@')",kTypeTable,type];
        judge=[self.fmdbDataBase executeUpdate:typeInsert];
    }
    else if(type&&subtype) {
        NSString *typeIdSelect=[NSString stringWithFormat:@"select typeInfo_ID from %@ where typename='%@'",kTypeTable,type];
        int typeId=[self.fmdbDataBase intForQuery:typeIdSelect];

        
        NSString *subtypeIns=[NSString stringWithFormat:@"insert into %@(subTypename,refer_ID) values('%@','%d')",kSubTypeTable,subtype,typeId];
        judge=[self.fmdbDataBase executeUpdate:subtypeIns];
    }
    return judge;
    
}
-(NSMutableDictionary *)putDatabaseInfoInDictOfKeyMonth
{
    NSMutableDictionary *dict=[[NSMutableDictionary alloc] init];
    for (int i=1; i<=12; i++)
    {
        
    //acount,typeAndSubtype,time,other,month
    NSString *findInfo=[NSString stringWithFormat:@"select * from %@ where month=%d  ORDER BY time DESC",kTableName,i];
        FMResultSet *result =[self.fmdbDataBase executeQuery:findInfo];
        while ([result next]) {
            BIDABill *aPayout=[[BIDABill alloc] init];
            aPayout.Camount=[result stringForColumn:@"amount"];
            aPayout.payoutId=[result intForColumn:@"id"];
            aPayout.type=[result stringForColumn:@"type"];
            aPayout.Ctime=[result stringForColumn:@"time"];
            aPayout.Cdescription=[result stringForColumn:@"other"];
            aPayout.Cmonth=[result intForColumn:@"month"];
            aPayout.subType=[result stringForColumn:@"subType"];
            if ([dict objectForKey:[NSNumber numberWithInt:i]]) {
                NSMutableArray *arr=[dict objectForKey:[NSNumber numberWithInt:i]];
                [arr addObject:aPayout];
                
            }
            else
            {
                NSMutableArray *arr=[[NSMutableArray alloc] init];
                [arr addObject:aPayout];
                [dict setObject:arr forKey:[NSNumber numberWithInt:i]];
                [arr release];
            }
        }
        
    }
    return dict;
    [dict release];
}

-(BOOL)updataThePayout:(BIDABill *)payout_id
{
   
   BOOL juad=(BOOL)[self deleteAPayout:payout_id];
    BOOL  judge=NO;
    if (juad==YES)
    {
      
        judge=[self insertAPayoutOrTypeOrSubtype:payout_id OrType:nil orSubtype:nil];
         
        
    }
   
   return judge;
}
-(BOOL)deleteAPayout:(BIDABill*)payout_id
{
    NSString *delete=[NSString stringWithFormat:@"delete from %@ where id =%d",kTableName,payout_id.payoutId];
    
    BOOL judge=[self.fmdbDataBase executeUpdate:delete];
    return judge;
    
}
-(NSMutableDictionary *)putDatabaseInfoInDictOfType
{
    NSMutableDictionary *dict=[[NSMutableDictionary alloc] init];
    NSMutableArray *hadTypes=[self readDatabaseHadTheType];
    int count=[hadTypes count];
    for (int i=0; i<count; i++) {
        NSString *findInfo=[NSString stringWithFormat:@"select * from %@ where type='%@'  ORDER BY time DESC",kTableName,[hadTypes objectAtIndex:i]];
       FMResultSet *typeInfo= [self.fmdbDataBase executeQuery:findInfo];
        while ([typeInfo next]) {
            BIDABill *aPayout=[[BIDABill alloc] init];
            aPayout.Camount=[typeInfo stringForColumn:@"amount"];
            aPayout.payoutId=[typeInfo intForColumn:@"id"];
            aPayout.type=[typeInfo stringForColumn:@"type"];
            aPayout.Ctime=[typeInfo stringForColumn:@"time"];
            aPayout.Cdescription=[typeInfo stringForColumn:@"other"];
            aPayout.Cmonth=[typeInfo intForColumn:@"month"];
            aPayout.subType=[typeInfo stringForColumn:@"subType"];
            if ([dict objectForKey:[hadTypes objectAtIndex:i]]) {
                NSMutableArray *array=[dict objectForKey:[hadTypes objectAtIndex:i]];
                [array addObject:aPayout];
                
            }
            else
            {
                NSMutableArray *array=[[NSMutableArray alloc] init];
                [array addObject:aPayout];
                [dict setObject:array forKey:[hadTypes objectAtIndex:i]];
                [array release];
            }
        }
        
    }
    return dict;
    [dict release];
}
-(NSMutableArray *)readDatabaseHadTheType
{
    NSMutableArray *array=[[NSMutableArray alloc] init];
       NSString *desc=[NSString stringWithFormat:@"select type from %@ order by type DESC ",kTableName];
    NSString *type=[NSString stringWithFormat:@"select DISTINCT type from (%@) ",desc];
    FMResultSet *typeAnd=[self.fmdbDataBase executeQuery:type];
    while ([typeAnd next]) {
       
        NSString *aType=[typeAnd stringForColumn:@"type"];
        
        [array addObject:aType ];
    }
    return array;
    [array release];
}
#pragma mark--pie circle
-(NSMutableArray *)readAMonthData:(int)nomth
{
   
    
    
    NSMutableArray *array=[[NSMutableArray alloc] init];
    NSString *findInfo=[NSString stringWithFormat:@"select * from %@ where month=%d  ORDER BY time DESC",kTableName,nomth];
    FMResultSet  *result=[self.fmdbDataBase executeQuery:findInfo];
    while ([result next])
    {
        BIDABill *aPayout=[[BIDABill alloc] init];
        aPayout.Camount=[result stringForColumn:@"amount"];
        aPayout.CtypeAndSubtype=[result stringForColumn:@"typeAndSubtype"];
        aPayout.Ctime=[result stringForColumn:@"time"];
        aPayout.Cdescription=[result stringForColumn:@"other"];
        aPayout.Cmonth=[result intForColumn:@"month"];
        aPayout.payoutId=[result intForColumn:@"id"];
        [array addObject:aPayout];
    }
    return array;
    [array release];
}
-(NSMutableArray *)readDatabaseHadTheTypeInMonth:(int)month
{
    NSMutableArray *array=[[NSMutableArray alloc] init];
    NSString *desc=[NSString stringWithFormat:@"select type from %@ order by type DESC where typeId =%d ",kTableName,month];
    NSString *type=[NSString stringWithFormat:@"select DISTINCT type from (%@) ",desc];
    FMResultSet *typeAnd=[self.fmdbDataBase executeQuery:type];
    while ([typeAnd next]) 
    {
        
        NSString *aType=[typeAnd stringForColumn:@"type"];
        
        [array addObject:aType ];
    }
    return array;
    [array release];
}
-(int)readDatabaseAcountOfAType:(NSString *)aType inMonth:(int)month
{
    
    int count=0;
     NSString *desc=[NSString stringWithFormat:@"select sum(amount) from %@ order by type DESC where typeId =%d and type='%@'",kTableName,month,aType];
    double total=[self.fmdbDataBase doubleForQuery:desc];
    
    NSLog(@"%f",total);
//   FMResultSet *result=[self.fmdbDataBase executeQuery:desc];
//
//    while ([result next]) {
//        count=[result doubleForColumnIndex:0];
//    }
//    
    
    return count;
    
    
}
-(NSMutableArray *)getInfoOfType:(NSString *)type inMonth:(int)month
{
    NSMutableArray*array=[[NSMutableArray alloc] init];
  
    NSString *findInfo=[NSString stringWithFormat:@"select * from %@ where month=%d and type='%@'  ORDER BY time DESC",kTableName,month,type];
    FMResultSet  *result=[self.fmdbDataBase executeQuery:findInfo];
    while ([result next])
    {
        BIDABill *aPayout=[[BIDABill alloc] init];
        aPayout.Camount=[result stringForColumn:@"amount"];
        aPayout.CtypeAndSubtype=[result stringForColumn:@"typeAndSubtype"];
        aPayout.Ctime=[result stringForColumn:@"time"];
        aPayout.Cdescription=[result stringForColumn:@"other"];
        aPayout.Cmonth=[result intForColumn:@"month"];
        aPayout.payoutId=[result intForColumn:@"id"];
        [array addObject:aPayout];
    }
    return array;
}
#pragma mark--type'sTable
-(BOOL)readPlistInfoToDatabase
{
    BOOL judge=NO;
    NSBundle *bundle=[NSBundle mainBundle];
    NSURL *plistURL=[bundle URLForResource:@"types"
                             withExtension:@"plist"];
    NSDictionary *dictionary=[NSDictionary 
                              dictionaryWithContentsOfURL:plistURL];
    
    NSArray *arr=[dictionary allKeys];
    for (int i=0; i<arr.count; i++)
    {
        NSString *aType=[arr objectAtIndex:i];
        BOOL judegr=[self insertAPayoutOrTypeOrSubtype:nil OrType:aType orSubtype:nil];
        if (judegr)
        {
            judge=YES;
           // NSLog(@"success");
        }
        NSArray *subType=[dictionary objectForKey:aType];
        for (int j=0; j<subType.count; j++) {
            NSString *string=[subType objectAtIndex:j];
            BOOL jud=[self insertAPayoutOrTypeOrSubtype:nil OrType:aType orSubtype:string];
            if (jud) {
                judge=YES;
              //  NSLog(@"subSuccess  ");
            }
            
        }
    }
    
 
    isReadDatabase=YES;
    [[NSUserDefaults standardUserDefaults] setValue:[NSNumber numberWithBool:isReadDatabase] forKey:kIsReadDatabase];
    [[NSUserDefaults standardUserDefaults] synchronize];
    
    return YES;
    
}
-(int)typeCountInDatabase
{
    int count=0;
    NSString *str=[NSString stringWithFormat:@"select count(*) from %@",kTypeTable];
    count=[self.fmdbDataBase intForQuery:str];
    return count;
}
-(NSMutableArray *)DatabaseHadTypes
{
    NSMutableArray *array=[[NSMutableArray alloc] init];
    NSString *str=[NSString stringWithFormat:@"select typename from %@ ",kTypeTable];
    FMResultSet *result=[self.fmdbDataBase executeQuery:str];
    while ([result next]) {
        NSString *aType=[result stringForColumn:@"typename"];
        [array addObject:aType];
    }
    return array;
    [array release];
}
-(NSMutableDictionary *)readDatabasePutIntoDict
{
    NSMutableDictionary *dict=[[NSMutableDictionary alloc] init];
   
    NSMutableArray *typeArr=[self DatabaseHadTypes];
    for (int i=0; i<typeArr.count;i++) {
        NSMutableArray *aSubTypeArr=[[NSMutableArray alloc] init];
        NSString *aType=[typeArr objectAtIndex:i];
         NSString *str=[NSString stringWithFormat:@"select * from %@ a,%@ b where a.refer_ID=b.typeInfo_ID and b.typename='%@'",kSubTypeTable,kTypeTable,aType];
        FMResultSet *ads=[self.fmdbDataBase executeQuery:str];
        while ([ads next]) {
            NSString *aSubType=[ads stringForColumn:@"subTypename"];
            [aSubTypeArr addObject:aSubType];
        }
      
        [dict setValue:aSubTypeArr forKey:aType];
        [aSubTypeArr release];
        
    }
    return dict;
    [dict release];
    
}
-(NSMutableArray *)readDatabaseInfoBecomeATypes
{
    NSMutableArray *arr=[[NSMutableArray alloc] init];
    NSMutableArray *types=[self DatabaseHadTypes];
    for (int i=0; i<types.count; i++) {
        NSString *type=[types objectAtIndex:i];
        NSString *typeIdSelect=[NSString stringWithFormat:@"select typeInfo_ID from %@ where typename='%@'",kTypeTable,type];
        NSInteger typeId=[self.fmdbDataBase intForQuery:typeIdSelect];
        NSString *subTypeStr=[NSString stringWithFormat:@"select * from %@ where refer_ID=%d ",kSubTypeTable,typeId];
        FMResultSet *end=[self.fmdbDataBase executeQuery:subTypeStr];
        while ([end next]) {
            BIDATypes *AType=[[BIDATypes alloc] init];
            AType.typeId=typeId;
            NSInteger subTypeId=[end intForColumn:@"subclassInfo_ID"];
            NSString *aSubType=[end stringForColumn:@"subTypename"];
            AType.subTypeId=subTypeId;
            AType.subType=aSubType;
            [arr addObject:AType];
            [AType release];
        }
    }
    return arr;
    [arr release];
}
-(BOOL)updataTheTypeOrSubType:(NSString *)type orSubtype:(NSString *)subtype inBeforeAType:(BIDATypes *)aType
{
    BOOL judge=NO;
    if (![type isEqualToString:aType.type] &&!subtype) {
        NSString *updataType=[NSString stringWithFormat:@"updata %@ set typename='%@' where typeInfo_ID=%d",kTypeTable,type,aType.typeId];
        judge=[self.fmdbDataBase executeUpdate:updataType];
        
    }
    if ([type isEqualToString:type]&&![subtype isEqualToString:aType.subType]) {
        NSString *updataSubType=[NSString stringWithFormat:@"updata %@ set "];
        
    }
    
    
    
}
#pragma mark---deal with
*/
@end
